Oracle indexek használata
Miért is fontos nekünk az indexelés ha a fejlesztői oldalon csücsülünk, és nem mi vagyunk a DBA szerepében? Azért mert a sebesség számunkra is döntő, hiszen a felhasználói igényeket elégítjük ki, akik számára a sebesség kulcs fontosságú lehet. Ne gondoljunk azonban az indexre úgy, hogy rátesszük a táblára és utána minden jó lesz, mert nem ilyen egyszerű a történet. Nézzünk meg ezért néhány alapelvet az indexek használata során:
- használjunk indexet minden olyan mezőn, amelyen WHERE feltételt használunk
- azokon a mezőkön, amelyeket JOIN során használjuk
- azokon a mezőkön, amelyek nagyszámú egyedi értéket tartalmaznak
- olyan mező, amely csak kis számú elérő értéket tartalmaz, ott Bitmap indexet használjunk
- INSERT, UPDATE, DELETE esetén az index használata lassítja a folyamatot, ezért olyan mezőket, amelyeket gyakran módosítunk, ne indexeljünk
- ha valamilyen funkciót használunk a WHERE feltételben (pl. matematikai műveletet), használjunk function-based indexet
- az idegen kulcsokat indexeljük be, ugyanis azok automatikusan nem indexelődnek
- ha kompozit indexet használunk (azaz több mezőt teszünk egy indexbe), akkor a SELECT során az összes, indexbe került mezőt használjuk
Az alábbiakban a leggyakoribb index típusokat nézzük át, mint a B-tree index, a Bitmap index és a Function-based index.
B-tree index
Ez az Oracle default index típusa, ha az index létrehozása során nem adunk meg további változókat, akkor automatikusan ez a típus jön létre. A neve "balanced tree"-t jelent, amely minden elemet ugyanannyi lépésben ér el. A működési elve a következő:
- a 125-ös értékkel rendelkező ID-t keressük az adatbázisban, ehhez első körben ahhoz a tartományhoz ugrik, amely tartalmazza ezt az értéket, azaz pl. a 100-200 közötti értékekhez.
- a következő szinten a 121-130 közötti tartományra ugrunk
- végül a 125-ös értéket kikeressük és visszaadjuk a hívó félnek.
Az index létrehozása nagyon egyszerűen végrehajtható:
CREATE INDEX idx_emp_id ON employee (id);Ahol az idx_emp_id az index elnevezése lesz, az employee maga a tábla amire létrehozzuk, az id pedig a mező, amit indexelünk.
Bitmap index
Ezt a típust akkor érdemes használni, ha - szemben az előző típussal - az adott mező értékei kis szórásúak, azaz pl. csak M vagy F értéke van. Az index itt úgy működik, hogy minden egyes rekordnak van egy egyedi ROWID azonosítója, amelyhez M=0 és F=1, vagy M=1 és F=0 érték párosul, attól függően, hogy az M vagy az F érték szerepel az adott mezőben. Ezáltal a lekérdezés gyorsasága is nagy mértékben megnő, viszont ebből következik az is, hogy ha növekszik a lehetséges értékek száma, azzal az index gyorsasága csökken.
Az index létrehozása:
CREATE BITMAP INDEX idx_emp_gender ON employee (gender);Function-based index
Ezt a típusú indexet egy function vagy expression eredményére használhatjuk. Alapesetben, ha futtatunk egy SELECT lekérdezést, benne pl. egy matematikai funkcióval, akkor hiába van index a forrástáblán, a SELECT maga lassú marad, mert az átvett értéken műveletet hajtunk végre (pl. SELECT annual_salary / 12 FROM employee WHERE annual_salary / 12 >= 10000;). Erre hozhatunk létre egy indexet az alábbi módon:
CREATE INDEX idx_emp_mthsal ON employee (annual_salary / 12);Ha a fenti módon az indexet beállítottuk, onnantól kezdve az Oracle automatikusan az indexeket használva futtatja le a lekérdezéseket. Ennek keretében az Oracle nem direkt a táblából kérdez, hanem az indexet használva, ami sokkal gyorsabb elérést tesz lehetővé. Az Oracle automatikusan dönti el, hogy a lekérdezés során milyen, ún. index scanning módszert alkalmaz, az aktuálisan alkalmazott módszert megnézhetjük az EXPLAIN PLAN FOR SELECT (...) paranccsal. Ezek a módszerek a következők lehetnek:
- Index Full Scan: az Oracle a teljes indexet beolvassa, sorrendben. Elsősorban ORDER BY esetén fut le ez a módszer, amikor az index sorbarendezettsége megfelel a SELECT-nek. GROUP BY esetén pedig akkor, ha a SELECT-ben minden olyan mező benne van, amely indexelve lett.
- Index Fast Full Scan: amennyiben a SELECT során csak olyan mezőket kérünk le, amely indexelve lett, akkor a lekérdezés csak az indexből történik és nem az adatbázis tábla felől. Fontos viszont, hogy a mezők NOT NULL megkötést tartalmazzanak.
- Index Range Scan: ha a SELECT-ben van WHERE feltétel, akkor a legtöbb esetben ez kerül használatra.
- Index Unique Scan: szintén WHERE feltétel esetén kerül használatra, azonban csak abban az esetben, ha a mező minden eleme különböző. Ekkor az első megtalált elemet visszaadja, ami a WHERE feltételben benne volt, mivel másik elem már nem lehet az adattáblában ugyanazzal az értékkel (ilyenek pl. az ID mezők).
- Index Skip Scan: amennyiben az indexet úgy hoztuk létre, hogy egy index kompozit, azaz több mezőt is tartalmaz, a SELECT során viszont csak az egyik mezőre történik WHERE feltétel.
- Full Table Scan: ebben az esetben az Oracle nem használ indexet, direkt az adattáblához nyúl. Ebben az esetben nincsen index az adattáblánkon. Amennyiben használjuk az EXPLAIN PLAN módszert, és ilyent látunk, akkor a performancián tudunk javítani azzal, hogy az ilyen táblákat - ha szükséges - indexekkel látjuk el.
